﻿CREATE PROCEDURE [dbo].[proc_Statistics_Outlay]
	(
		@CompanyId int,
		@CateOneId int,
		@CateTwoId int,
		@StartDate nchar(10),
		@EndDate nchar(10)
	)
AS
	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY CateOneId)AS Row
		,s.Id As SubjectId
		,s.Title As SubjectName
		,s.CateOneId
		,(Select Title From Finance_Subject_Parent Where Id=s.CateOneId) As CateOneName
		,s.CateTwoId
		,(Select Title From Finance_Subject_Parent Where Id=s.CateTwoId) As CateTwoName
		,(Select Count(OutNum) From Finance_Outlay_Record Where (@CompanyId=0 Or CompanyId=@CompanyId) and Convert(nchar(10),CreateDate,120) between @StartDate and @EndDate) As OutNum
		,ISNULL((Select Sum(OutPrice) From Finance_Outlay_Record Where (@CompanyId=0 Or CompanyId=@CompanyId) and Convert(nchar(10),CreateDate,120) between @StartDate and @EndDate),0) As OutPrice
	From Finance_Subject s
	)
	Select * From list
	RETURN
